package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.dao.*;
import java.sql.*;
import java.util.*;

/*******************************************************************************
 *
 *        @author Adam
 *          Date: Mar 3, 2009
 *   Description:
 * Last Revision: Mar 3, 2009
 *
 ******************************************************************************/

public class ServiceDAO {

    /////////////////////////////////////////////
    ///   Singleton code
    private static ServiceDAO instance = null;

    /** Creates a new instance of WarrantyDAO */
    private ServiceDAO() {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized ServiceDAO getInstance() {
        if (instance == null) {
            instance = new ServiceDAO();
        }
        return instance;
    }//getInstance


    ////////////////////////////////////////////
    ///   CREATE methods
    /** Creates a new service in the database */
    public Service create(String id) throws DataException {
        Service service = new Service(id);
        service.setObjectAlreadyInDB(false);
        Cache.getInstance().put(service.getId(), service);
        return service;
    }//create

    ////////////////////////////////////////////
    ///   READ methods
    /** Reads an existing service from the database */
    public Service read(String id) throws DataException {
        if (Cache.getInstance().containsKey(id)) {
            return (Service) Cache.getInstance().get(id);
        }
        Connection conn = ConnectionPool.getInstance().get();
        try {
            return read(id, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the business object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to read an existing service from the database */
    synchronized Service read(String id, Connection conn) throws Exception {
        if (Cache.getInstance().containsKey(id)) {
            return (Service) Cache.getInstance().get(id);
        }
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM SERVICE where ID=?");
        try {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return readRecord(rs, conn);
            }//if
            throw new DataException("Service with id '" + id + "' not found.");
        } finally {
            stmt.close();
        }
    }//read

    /** Reads an existing Warranty from the database */
    public List<Service> readBy(String colName, String item) throws DataException {
        Connection conn = ConnectionPool.getInstance().get();
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM SERVICE where " + colName + "=? ORDER BY ID");
            stmt.setString(1, item);
            return search(stmt, conn);
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the database transaction!", e2);
            }
            throw new DataException("An error occurred while reading the business object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to create a service object from a record */
    synchronized Service readRecord(ResultSet rs, Connection conn) throws Exception {
        if (Cache.getInstance().containsKey(rs.getString("ID"))) {
            return (Service) Cache.getInstance().get(rs.getString("ID"));
        }
        Service service = new Service(rs.getString("ID"));
        service.setObjectAlreadyInDB(true);
        Cache.getInstance().put(service.getId(), service);
        service.setSE_DATE(rs.getTimestamp("SE_DATE"));
        service.setTOTAL_PRICE(rs.getDouble("TOTAL_PRICE"));
        service.setTax(rs.getDouble("TAX"));
        service.setCustomerid(rs.getString("ID"));
        service.setTechnicianid(rs.getString("ID"));
        service.setPhysicalcarid(rs.getString("ID"));
        service.setServiceFacilityid(rs.getString("ID"));
        service.setDirty(false);

        return service;
    }//readRecord

    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing service in the database */
    public void save(Service service) throws DataException {
        Connection conn = ConnectionPool.getInstance().get();
        try {
            save(service, conn);
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the business object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }//update

    /** Internal method to update a service in the database */
    void save(Service service, Connection conn) throws Exception {
        Cache.getInstance().put(service.getId(), service);
        if (service.isObjectAlreadyInDB() && service.isDirty()) {
            update(service, conn);
        } else if (service.isDirty()) {
            insert(service, conn);
        }//if
    }//save

    /** Saves an existing service to the database */
    private void update(Service service, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("UPDATE SERVICE SET SE_DATE=?, TAX=?, ID=?, ID=?, ID=?, ID=? WHERE ID=?");
        try {
            stmt.setTimestamp(1, service.getSE_DATE());
            stmt.setDouble(2, service.getTax());
            stmt.setString(3, service.getCustomerid());
            stmt.setString(4, service.getCustomerid());
            stmt.setString(5, service.getTechnicianid());
            stmt.setString(6, service.getPhysicalcarid());
            stmt.setString(7, service.getServiceFacilityid());
            stmt.setString(8, service.getId());
            stmt.execute();
            service.setDirty(false);
        } finally {
            stmt.close();
        }
    }

    /** Inserts a new service into the database */
    private void insert(Service service, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO SERVICE (ID, SE_DATE, TAX, ID, ID, ID, ID) VALUES (?, ?, ?, ?, ?, ?, ?)");
        try {
            stmt.setString(1, service.getId());
            stmt.setTimestamp(2, service.getSE_DATE());
            stmt.setDouble(3, service.getTax());
            stmt.setString(4, service.getCustomerid());
            stmt.setString(5, service.getCustomerid());
            stmt.setString(6, service.getTechnicianid());
            stmt.setString(7, service.getPhysicalcarid());
            stmt.setString(8, service.getServiceFacilityid());
            stmt.execute();
            service.setObjectAlreadyInDB(true);
            service.setDirty(false);
        } finally {
            stmt.close();
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(Service service) throws DataException {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods
    /** Retrieves all services from the database */
    public List<Service> getAll() throws DataException {
        Connection conn = ConnectionPool.getInstance().get();
        try {
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM SERVICE ORDER BY ID");
            return search(stmt, conn);
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the database transaction!", e2);
            }
            throw new DataException("An error occurred while reading the business object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to search by certain fields */
    private List<Service> search(PreparedStatement stmt, Connection conn) throws Exception {
        List<Service> services = new LinkedList<Service>();
        try {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                services.add(readRecord(rs, conn));
            }
        } finally {
            stmt.close();
        }
        return services;
    }
}//class